import pandas as pd
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

# 初始化 Flask 和 SQLAlchemy
app = Flask(__name__)
# 数据库配置
HOSTNAME = "127.0.0.1"
# 端口号
PORT = 3306
# 账号
USERNAME = "root"
# 密码
PASSWORD = 1234
# 数据库名
DATABASE = "test20250320"
app.config["SQLALCHEMY_DATABASE_URI"] = f'mysql+pymysql://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8'
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

db = SQLAlchemy(app)


# 定义数据库模型
class CMAParams(db.Model):
    __tablename__ = "CMAParams"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    category = db.Column(db.String(100))
    parameterName = db.Column(db.String(100))
    standardMethod = db.Column(db.String(255))
    standardNo = db.Column(db.String(50), unique=True)
    actualLimit = db.Column(db.String(100))
    description = db.Column(db.String(255))
    qualificationDate = db.Column(db.String(50))
    exportError = db.Column(db.String(100))
    remarks = db.Column(db.String(100))


# 确保表创建
def create_tables():
    with app.app_context():
        db.create_all()


# 获取已有的 standardNo，防止重复导入
def get_existing_standardNos():
    with app.app_context():
        return {row.standardNo for row in CMAParams.query.with_entities(CMAParams.standardNo).all()}


# 读取 Excel 并导入数据库
def import_excel_to_db(file_path):
    df = pd.read_excel(file_path, engine="openpyxl")

    # 映射列名
    column_mapping = {
        "类别(产品/检测对象)": "category",
        "项目/参数名称": "parameterName",
        "依据的标准（方法）名称及编号（含年号）": "standardMethod",
        "标准号": "standardNo",
        "实际限制范围": "actualLimit",
        "说明": "description",
        "取得资质日期": "qualificationDate",
        "导出限制范围有误的": "exportError",
        "备注（生物）": "remarks",
    }
    df.rename(columns=column_mapping, inplace=True)

    # 显式转换 float64 列为字符串，防止 fillna("") 报错
    for col in df.select_dtypes(include=["float64"]).columns:
        df[col] = df[col].astype(str)
    df.fillna("", inplace=True)  # 处理空值

    with app.app_context():
        existing_standardNos = get_existing_standardNos()
        records = [CMAParams(**row) for _, row in df.iterrows() if row["standardNo"] not in existing_standardNos]
        db.session.bulk_save_objects(records)
        db.session.commit()
        print("数据导入成功！")


# 执行创建表并导入数据
create_tables()
import_excel_to_db("C:/Users/wtf/Desktop/CMA能力参数导出-2024-12-27导出原版_-_拆分标准号-2024全-生物20250321.xlsx")
